package com.ruoyi.service.jpjs.rlexportdata.impl;

import com.ruoyi.mapper.jpjs.PublicMapper;
import com.ruoyi.mapper.jpjs.attendance.AttendanceMapper;
import com.ruoyi.mapper.jpjs.empchange.EmpChangeMapper;
import com.ruoyi.mapper.jpjs.emponjob.EmpOnJobMapper;
import com.ruoyi.mapper.jpjs.empquit.EmpQuitMapper;
import com.ruoyi.mapper.jpjs.recruitment.RecruitmentMapper;
import com.ruoyi.mapper.jpjs.socialpayment.SocialPaymentMapper;
import com.ruoyi.service.jpjs.rlexportdata.RlExportDataOneService;
import com.ruoyi.service.jpjs.rlexportdata.RlExportDataTowService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author zeo
 * @explain XXX
 * @date 2020/6/11 10:03
 */
@Service
public class RlExportDataTowServerImpl implements RlExportDataTowService {

    @Autowired
    PublicMapper publicMapper;

    @Autowired
    RecruitmentMapper recruitmentMapper;

    @Autowired
    AttendanceMapper attendanceMapper;

    @Autowired
    SocialPaymentMapper socialPaymentMapper;

    @Override
    public void exportRecruitment(HSSFWorkbook wb, String years, String months, List<Map<String, Object>> maps) {
        HSSFSheet sheet = wb.createSheet("招聘情况分析");
        HSSFRow row = sheet.createRow(0);
        sheet.setDefaultRowHeightInPoints(10);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 15));
        HSSFCellStyle txtStyle = wb.createCellStyle();
        HSSFFont txtFont = wb.createFont();
        txtFont.setFontName("宋体");
        txtFont.setFontHeightInPoints((short) 20);
        txtFont.setBold(true);
        txtStyle.setFont(txtFont);
        row.createCell(1).setCellValue("金鹏建设集团  " + years + "  年 " + months + "  月人力资源统计月报表");
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle);
        }

        row = sheet.createRow(1);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 16));
        HSSFCellStyle txtStyle1 = wb.createCellStyle();
        HSSFFont txtFont1 = wb.createFont();
        txtFont1.setFontName("宋体");
        txtFont1.setFontHeightInPoints((short) 16);
        txtFont1.setBold(true);
        txtStyle1.setFont(txtFont1);
        txtStyle1.setAlignment(HorizontalAlignment.CENTER);
        row.createCell(0).setCellValue("四、招聘情况分析");
        row.createCell(1);
        row.createCell(2);
        row.createCell(3);
        row.createCell(4);
        row.createCell(5);
        row.createCell(6);
        row.createCell(7);
        row.createCell(8);
        row.createCell(9);
        row.createCell(10);
        row.createCell(11);
        row.createCell(12);
        row.createCell(13);
        row.createCell(14);
        row.createCell(15);
        row.createCell(16);

        txtStyle1.setBorderBottom(BorderStyle.THIN);
        txtStyle1.setBorderLeft(BorderStyle.THIN);
        txtStyle1.setBorderTop(BorderStyle.THIN);
        txtStyle1.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle1);
        }

        row = sheet.createRow(2);
        HSSFCellStyle txtStyle2 = wb.createCellStyle();
        HSSFFont txtFont2 = wb.createFont();
        txtFont2.setFontName("宋体");
        txtFont2.setFontHeightInPoints((short) 11);
        txtStyle2.setFont(txtFont2);
        txtStyle2.setAlignment(HorizontalAlignment.CENTER);
        txtStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 5, 5));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 7, 7));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 16));
        row.createCell(0).setCellValue("单位");
        row.createCell(1).setCellValue("需求人数");
        row.createCell(2).setCellValue("电联人数");
        row.createCell(3).setCellValue("邀约人数");
        row.createCell(4).setCellValue("面试人数");
        row.createCell(5).setCellValue("约定上岗人数");
        row.createCell(6).setCellValue("实际到岗人数");
        row.createCell(7).setCellValue("试用合格人数");
        row.createCell(8).setCellValue("招聘达成率");
        row.createCell(9).setCellValue("面试者招聘渠道分析");
        row.createCell(10);
        row.createCell(11);
        row.createCell(12);
        row.createCell(13);
        row.createCell(14);
        row.createCell(15);
        row.createCell(16);
        txtStyle2.setBorderBottom(BorderStyle.THIN);
        txtStyle2.setBorderLeft(BorderStyle.THIN);
        txtStyle2.setBorderTop(BorderStyle.THIN);
        txtStyle2.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        row = sheet.createRow(3);
        row.createCell(0);
        row.createCell(1);
        row.createCell(2);
        row.createCell(3);
        row.createCell(4);
        row.createCell(5);
        row.createCell(6);
        row.createCell(7);
        row.createCell(8);
        row.createCell(9).setCellValue("猎聘");
        row.createCell(10).setCellValue("智联招聘");
        row.createCell(11).setCellValue("前程无忧");
        row.createCell(12).setCellValue("新安人才网");
        row.createCell(13).setCellValue("建筑英才网");
        row.createCell(14).setCellValue("其它网站");
        row.createCell(15).setCellValue("猎头公司");
        row.createCell(16).setCellValue("内部推荐");
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        /**
         * 后端拿取自身权限所拥有的工程公司
         */
        int i = 4;
        Map<String, Object> parameter = new HashMap<String, Object>();
        parameter.put("years", years);
        parameter.put("months", months);
        parameter.put("compList", maps);
        for (Map map : maps) {
            parameter.put("compId", map.get("compId"));
            List<Map<String, Object>> mapList = recruitmentMapper.list(parameter);
            row = sheet.createRow(i);
            if (mapList.size() > 1) {
                Map<String, Object> map1 = mapList.get(0);
                row.createCell(0).setCellValue(map1.get("compName").toString());
                row.createCell(1).setCellValue(map1.get("demandNum").toString());
                row.createCell(2).setCellValue(map1.get("teleconnectionNum").toString());
                row.createCell(3).setCellValue(map1.get("invitationNum").toString());
                row.createCell(4).setCellValue(map1.get("interviewNum").toString());
                row.createCell(5).setCellValue(map1.get("appointmentNum").toString());
                row.createCell(6).setCellValue(map1.get("actualArrivalNum").toString());
                row.createCell(7).setCellValue(map1.get("trialQualifiedNum").toString());
                row.createCell(8).setCellValue(map1.get("recruitReach").toString());
                row.createCell(9).setCellValue(map1.get("lpNum").toString());
                row.createCell(10).setCellValue(map1.get("zlzpNum").toString());
                row.createCell(11).setCellValue(map1.get("qcwyNum").toString());
                row.createCell(12).setCellValue(map1.get("xarcwNum").toString());
                row.createCell(13).setCellValue(map1.get("jzycwNum").toString());
                row.createCell(14).setCellValue(map1.get("qtNum").toString());
                row.createCell(15).setCellValue(map1.get("ltgsNum").toString());
                row.createCell(16).setCellValue(map1.get("nbtjNum").toString());
            }else {
                row.createCell(0).setCellValue(publicMapper.findRoleName(map.get("compId").toString()));
                row.createCell(1).setCellValue(0);
                row.createCell(2).setCellValue(0);
                row.createCell(3).setCellValue(0);
                row.createCell(4).setCellValue(0);
                row.createCell(5).setCellValue(0);
                row.createCell(6).setCellValue(0);
                row.createCell(7).setCellValue(0);
                row.createCell(8).setCellValue(0);
                row.createCell(9).setCellValue(0);
                row.createCell(10).setCellValue(0);
                row.createCell(11).setCellValue(0);
                row.createCell(12).setCellValue(0);
                row.createCell(13).setCellValue(0);
                row.createCell(14).setCellValue(0);
                row.createCell(15).setCellValue(0);
                row.createCell(16).setCellValue(0);
            }
            for (Cell cell : row) {
                cell.setCellStyle(txtStyle2);
            }
            i++;
        }

        //合计
        HSSFCellStyle txtStyle3 = wb.createCellStyle();
        HSSFFont txtFont3 = wb.createFont();
        txtFont3.setFontName("宋体");
        txtFont3.setFontHeightInPoints((short) 11);
        txtFont3.setBold(true);
        txtStyle3.setFont(txtFont3);
        txtStyle3.setAlignment(HorizontalAlignment.CENTER);
        txtStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
        txtStyle3.setBorderBottom(BorderStyle.THIN);
        txtStyle3.setBorderLeft(BorderStyle.THIN);
        txtStyle3.setBorderTop(BorderStyle.THIN);
        txtStyle3.setBorderRight(BorderStyle.THIN);
        row = sheet.createRow(i);
        Map map = recruitmentMapper.findTotalNum(years, months,maps);
        row.createCell(0).setCellValue("合计");
        row.createCell(1).setCellValue(map.get("demandNum").toString());
        row.createCell(2).setCellValue(map.get("teleconnectionNum").toString());
        row.createCell(3).setCellValue(map.get("invitationNum").toString());
        row.createCell(4).setCellValue(map.get("interviewNum").toString());
        row.createCell(5).setCellValue(map.get("appointmentNum").toString());
        row.createCell(6).setCellValue(map.get("actualArrivalNum").toString());
        row.createCell(7).setCellValue(map.get("trialQualifiedNum").toString());
        row.createCell(8).setCellValue(map.get("recruitReach").toString());
        row.createCell(9).setCellValue(map.get("lpNum").toString());
        row.createCell(10).setCellValue(map.get("zlzpNum").toString());
        row.createCell(11).setCellValue(map.get("qcwyNum").toString());
        row.createCell(12).setCellValue(map.get("xarcwNum").toString());
        row.createCell(13).setCellValue(map.get("jzycwNum").toString());
        row.createCell(14).setCellValue(map.get("qtNum").toString());
        row.createCell(15).setCellValue(map.get("ltgsNum").toString());
        row.createCell(16).setCellValue(map.get("nbtjNum").toString());
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle3);
        }

        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 14 * 256);
        sheet.setColumnWidth(2, 14 * 256);
        sheet.setColumnWidth(3, 14 * 256);
        sheet.setColumnWidth(4, 14 * 256);
        sheet.setColumnWidth(5, 15 * 256);
        sheet.setColumnWidth(6, 15 * 256);
        sheet.setColumnWidth(7, 15 * 256);
        sheet.setColumnWidth(8, 14 * 256);
        sheet.setColumnWidth(10, 14 * 256);
        sheet.setColumnWidth(11, 14 * 256);
        sheet.setColumnWidth(12, 14 * 256);
        sheet.setColumnWidth(13, 14 * 256);
        sheet.setColumnWidth(14, 14 * 256);
        sheet.setColumnWidth(15, 14 * 256);
        sheet.setColumnWidth(16, 14 * 256);

    }

    @Override
    public void exportAttendance(HSSFWorkbook wb, String years, String months, List<Map<String, Object>> maps) {
        HSSFSheet sheet = wb.createSheet("考勤情况分析");
        HSSFRow row = sheet.createRow(0);
        sheet.setDefaultRowHeightInPoints(10);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 15));
        HSSFCellStyle txtStyle = wb.createCellStyle();
        HSSFFont txtFont = wb.createFont();
        txtFont.setFontName("宋体");
        txtFont.setFontHeightInPoints((short) 20);
        txtFont.setBold(true);
        txtStyle.setFont(txtFont);
        row.createCell(1).setCellValue("金鹏建设集团  " + years + "  年 " + months + "  月人力资源统计月报表");
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle);
        }

        row = sheet.createRow(1);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 16));
        HSSFCellStyle txtStyle1 = wb.createCellStyle();
        HSSFFont txtFont1 = wb.createFont();
        txtFont1.setFontName("宋体");
        txtFont1.setFontHeightInPoints((short) 16);
        txtFont1.setBold(true);
        txtStyle1.setFont(txtFont1);
        txtStyle1.setAlignment(HorizontalAlignment.CENTER);
        row.createCell(0).setCellValue("五、考勤情况分析");
        row.createCell(1);
        row.createCell(2);
        row.createCell(3);
        row.createCell(4);
        row.createCell(5);
        row.createCell(6);
        row.createCell(7);
        row.createCell(8);
        row.createCell(9);
        row.createCell(10);
        row.createCell(11);
        row.createCell(12);
        row.createCell(13);
        row.createCell(14);
        row.createCell(15);
        row.createCell(16);

        txtStyle1.setBorderBottom(BorderStyle.THIN);
        txtStyle1.setBorderLeft(BorderStyle.THIN);
        txtStyle1.setBorderTop(BorderStyle.THIN);
        txtStyle1.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle1);
        }

        row = sheet.createRow(2);
        HSSFCellStyle txtStyle2 = wb.createCellStyle();
        HSSFFont txtFont2 = wb.createFont();
        txtFont2.setFontName("宋体");
        txtFont2.setFontHeightInPoints((short) 11);
        txtStyle2.setFont(txtFont2);
        txtStyle2.setAlignment(HorizontalAlignment.CENTER);
        txtStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 2));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 3, 4));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 6));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 7, 8));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 10));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 11, 12));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 13, 14));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 15, 16));

        row.createCell(0).setCellValue("单位");
        row.createCell(1).setCellValue("迟到");
        row.createCell(2);
        row.createCell(3).setCellValue("早退");
        row.createCell(4);
        row.createCell(5).setCellValue("旷工");
        row.createCell(6);
        row.createCell(7).setCellValue("事假");
        row.createCell(8);
        row.createCell(9).setCellValue("病假");
        row.createCell(10);
        row.createCell(11).setCellValue("婚假");
        row.createCell(12);
        row.createCell(13).setCellValue("丧假");
        row.createCell(14);
        row.createCell(15).setCellValue("产假");
        row.createCell(16);
        txtStyle2.setBorderBottom(BorderStyle.THIN);
        txtStyle2.setBorderLeft(BorderStyle.THIN);
        txtStyle2.setBorderTop(BorderStyle.THIN);
        txtStyle2.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        row = sheet.createRow(3);
        row.createCell(0);
        row.createCell(1).setCellValue("人数");
        row.createCell(2).setCellValue("时数");
        row.createCell(3).setCellValue("人数");
        row.createCell(4).setCellValue("时数");
        row.createCell(5).setCellValue("人数");
        row.createCell(6).setCellValue("时数");
        row.createCell(7).setCellValue("人数");
        row.createCell(8).setCellValue("时数");
        row.createCell(9).setCellValue("人数");
        row.createCell(10).setCellValue("时数");
        row.createCell(11).setCellValue("人数");
        row.createCell(12).setCellValue("时数");
        row.createCell(13).setCellValue("人数");
        row.createCell(14).setCellValue("时数");
        row.createCell(15).setCellValue("人数");
        row.createCell(16).setCellValue("时数");
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        /**
         * 后端拿取自身权限所拥有的工程公司
         */
        int i = 4;
        Map<String, Object> parameter = new HashMap<String, Object>();
        parameter.put("years", years);
        parameter.put("months", months);
        parameter.put("compList", maps);

        for (Map map : maps) {
            parameter.put("compId", map.get("compId"));
            List<Map<String, Object>> mapList = attendanceMapper.list(parameter);
            row = sheet.createRow(i);
            if (mapList.size() > 1) {
                Map<String, Object> map1 = mapList.get(0);
                row.createCell(0).setCellValue(map1.get("compName").toString());
                row.createCell(1).setCellValue(map1.get("lateNum").toString());
                row.createCell(2).setCellValue(map1.get("lateHours").toString());
                row.createCell(3).setCellValue(map1.get("leaveNum").toString());
                row.createCell(4).setCellValue(map1.get("leaveHours").toString());
                row.createCell(5).setCellValue(map1.get("absenteeismNum").toString());
                row.createCell(6).setCellValue(map1.get("absenteeismHours").toString());
                row.createCell(7).setCellValue(map1.get("compassionateNum").toString());
                row.createCell(8).setCellValue(map1.get("compassionateHours").toString());
                row.createCell(9).setCellValue(map1.get("sickNum").toString());
                row.createCell(10).setCellValue(map1.get("sickHours").toString());
                row.createCell(11).setCellValue(map1.get("marriageNum").toString());
                row.createCell(12).setCellValue(map1.get("marriageHours").toString());
                row.createCell(13).setCellValue(map1.get("mourningNum").toString());
                row.createCell(14).setCellValue(map1.get("mourningHours").toString());
                row.createCell(15).setCellValue(map1.get("yieldNum").toString());
                row.createCell(16).setCellValue(map1.get("yieldHours").toString());
            }else {
                row.createCell(0).setCellValue(publicMapper.findRoleName(map.get("compId").toString()));
                row.createCell(1).setCellValue(0);
                row.createCell(2).setCellValue(0);
                row.createCell(3).setCellValue(0);
                row.createCell(4).setCellValue(0);
                row.createCell(5).setCellValue(0);
                row.createCell(6).setCellValue(0);
                row.createCell(7).setCellValue(0);
                row.createCell(8).setCellValue(0);
                row.createCell(9).setCellValue(0);
                row.createCell(10).setCellValue(0);
                row.createCell(11).setCellValue(0);
                row.createCell(12).setCellValue(0);
                row.createCell(13).setCellValue(0);
                row.createCell(14).setCellValue(0);
                row.createCell(15).setCellValue(0);
                row.createCell(16).setCellValue(0);
            }
            for (Cell cell : row) {
                cell.setCellStyle(txtStyle2);
            }
            i++;
        }
        //合计
        HSSFCellStyle txtStyle3 = wb.createCellStyle();
        HSSFFont txtFont3 = wb.createFont();
        txtFont3.setFontName("宋体");
        txtFont3.setFontHeightInPoints((short) 11);
        txtFont3.setBold(true);
        txtStyle3.setFont(txtFont3);
        txtStyle3.setAlignment(HorizontalAlignment.CENTER);
        txtStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
        txtStyle3.setBorderBottom(BorderStyle.THIN);
        txtStyle3.setBorderLeft(BorderStyle.THIN);
        txtStyle3.setBorderTop(BorderStyle.THIN);
        txtStyle3.setBorderRight(BorderStyle.THIN);
        row = sheet.createRow(i);
        Map map = attendanceMapper.findTotalNum(years, months,maps);
        row.createCell(0).setCellValue("合计");
        row.createCell(1).setCellValue(map.get("lateNum").toString());
        row.createCell(2).setCellValue(map.get("lateHours").toString());
        row.createCell(3).setCellValue(map.get("leaveNum").toString());
        row.createCell(4).setCellValue(map.get("leaveHours").toString());
        row.createCell(5).setCellValue(map.get("absenteeismNum").toString());
        row.createCell(6).setCellValue(map.get("absenteeismHours").toString());
        row.createCell(7).setCellValue(map.get("compassionateNum").toString());
        row.createCell(8).setCellValue(map.get("compassionateHours").toString());
        row.createCell(9).setCellValue(map.get("sickNum").toString());
        row.createCell(10).setCellValue(map.get("sickHours").toString());
        row.createCell(11).setCellValue(map.get("marriageNum").toString());
        row.createCell(12).setCellValue(map.get("marriageHours").toString());
        row.createCell(13).setCellValue(map.get("mourningNum").toString());
        row.createCell(14).setCellValue(map.get("mourningHours").toString());
        row.createCell(15).setCellValue(map.get("yieldNum").toString());
        row.createCell(16).setCellValue(map.get("yieldHours").toString());
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle3);
        }
        sheet.setColumnWidth(0, 20 * 256);
    }

    @Override
    public void exportSocialPayment(HSSFWorkbook wb, String years, String months, List<Map<String, Object>> maps) {
        HSSFSheet sheet = wb.createSheet("合同社保公积金分析");
        HSSFRow row = sheet.createRow(0);
        sheet.setDefaultRowHeightInPoints(10);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 15));
        HSSFCellStyle txtStyle = wb.createCellStyle();
        HSSFFont txtFont = wb.createFont();
        txtFont.setFontName("宋体");
        txtFont.setFontHeightInPoints((short) 20);
        txtFont.setBold(true);
        txtStyle.setFont(txtFont);
        row.createCell(1).setCellValue("金鹏建设集团  " + years + "  年 " + months + "  月人力资源统计月报表");
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle);
        }

        row = sheet.createRow(1);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 11));
        HSSFCellStyle txtStyle1 = wb.createCellStyle();
        HSSFFont txtFont1 = wb.createFont();
        txtFont1.setFontName("宋体");
        txtFont1.setFontHeightInPoints((short) 16);
        txtFont1.setBold(true);
        txtStyle1.setFont(txtFont1);
        txtStyle1.setAlignment(HorizontalAlignment.CENTER);
        row.createCell(0).setCellValue("六、合同社保公积金分析");row.createCell(1);row.createCell(2);
        row.createCell(3);row.createCell(4);row.createCell(5);
        row.createCell(6);row.createCell(7);row.createCell(8);
        row.createCell(9);row.createCell(10);row.createCell(11);
        txtStyle1.setBorderBottom(BorderStyle.THIN);
        txtStyle1.setBorderLeft(BorderStyle.THIN);
        txtStyle1.setBorderTop(BorderStyle.THIN);
        txtStyle1.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle1);
        }

        row = sheet.createRow(2);
        HSSFCellStyle txtStyle2 = wb.createCellStyle();
        HSSFFont txtFont2 = wb.createFont();
        txtFont2.setFontName("宋体");
        txtFont2.setFontHeightInPoints((short) 11);
        txtStyle2.setFont(txtFont2);
        txtStyle2.setAlignment(HorizontalAlignment.CENTER);
        txtStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 3));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 4, 7));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 8, 11));
        row.createCell(0).setCellValue("单位");
        row.createCell(1).setCellValue("劳动合同情况");row.createCell(2);row.createCell(3);
        row.createCell(4).setCellValue("社保情况");row.createCell(5);row.createCell(6);
        row.createCell(7);row.createCell(8).setCellValue("公积金情况");row.createCell(9);
        row.createCell(10);row.createCell(11);
        txtStyle2.setBorderBottom(BorderStyle.THIN);
        txtStyle2.setBorderLeft(BorderStyle.THIN);
        txtStyle2.setBorderTop(BorderStyle.THIN);
        txtStyle2.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        row = sheet.createRow(3);
        row.createCell(0);
        row.createCell(1).setCellValue("新签");
        row.createCell(2).setCellValue("续签");
        row.createCell(3).setCellValue("终止");
        row.createCell(4).setCellValue("上月末参保人数");
        row.createCell(5).setCellValue("本月新增");
        row.createCell(6).setCellValue("本月减少");
        row.createCell(7).setCellValue("本月未参保人数");
        row.createCell(8).setCellValue("上月未参加人数");
        row.createCell(9).setCellValue("本月新增");
        row.createCell(10).setCellValue("本月减少");
        row.createCell(11).setCellValue("本月未参加人数");
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        /**
         * 后端拿取自身权限所拥有的工程公司
         */
        int i = 4;
        Map<String, Object> parameter = new HashMap<String, Object>();
        parameter.put("years", years);
        parameter.put("months", months);
        parameter.put("compList", maps);
        for (Map map : maps) {
            parameter.put("compId", map.get("compId"));
            List<Map<String, Object>> mapList = socialPaymentMapper.list(parameter);
            row = sheet.createRow(i);
            if (mapList.size() > 1) {
                Map<String, Object> map1 = mapList.get(0);
                row.createCell(0).setCellValue(map1.get("compName").toString());
                row.createCell(1).setCellValue(map1.get("newSignatureNum").toString());
                row.createCell(2).setCellValue(map1.get("renewalNum").toString());
                row.createCell(3).setCellValue(map1.get("terminationNum").toString());
                row.createCell(4).setCellValue(map1.get("lastMonthSocialNum").toString());
                row.createCell(5).setCellValue(map1.get("thisAddSocialNum").toString());
                row.createCell(6).setCellValue(map1.get("thisReduceSocialNum").toString());
                row.createCell(7).setCellValue(map1.get("thisMonthSocialNum").toString());
                row.createCell(8).setCellValue(map1.get("lastMonthFundNum").toString());
                row.createCell(9).setCellValue(map1.get("thisAddFundNum").toString());
                row.createCell(10).setCellValue(map1.get("thisReduceFundNum").toString());
                row.createCell(11).setCellValue(map1.get("thisMonthFundNum").toString());
            }else{
                row.createCell(0).setCellValue(publicMapper.findRoleName(map.get("compId").toString()));
                row.createCell(1).setCellValue(0);
                row.createCell(2).setCellValue(0);
                row.createCell(3).setCellValue(0);
                row.createCell(4).setCellValue(0);
                row.createCell(5).setCellValue(0);
                row.createCell(6).setCellValue(0);
                row.createCell(7).setCellValue(0);
                row.createCell(8).setCellValue(0);
                row.createCell(9).setCellValue(0);
                row.createCell(10).setCellValue(0);
                row.createCell(11).setCellValue(0);
            }
            for (Cell cell : row) {
                cell.setCellStyle(txtStyle2);
            }
            i++;
        }

        //合计
        HSSFCellStyle txtStyle3 = wb.createCellStyle();
        HSSFFont txtFont3 = wb.createFont();
        txtFont3.setFontName("宋体");
        txtFont3.setFontHeightInPoints((short) 11);
        txtFont3.setBold(true);
        txtStyle3.setFont(txtFont3);
        txtStyle3.setAlignment(HorizontalAlignment.CENTER);
        txtStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
        txtStyle3.setBorderBottom(BorderStyle.THIN);
        txtStyle3.setBorderLeft(BorderStyle.THIN);
        txtStyle3.setBorderTop(BorderStyle.THIN);
        txtStyle3.setBorderRight(BorderStyle.THIN);
        row = sheet.createRow(i);
        Map map = socialPaymentMapper.findTotalNum(years, months,maps);
        row.createCell(0).setCellValue("合计");
        row.createCell(1).setCellValue(map.get("newSignatureNum").toString());
        row.createCell(2).setCellValue(map.get("renewalNum").toString());
        row.createCell(3).setCellValue(map.get("terminationNum").toString());
        row.createCell(4).setCellValue(map.get("lastMonthSocialNum").toString());
        row.createCell(5).setCellValue(map.get("thisAddSocialNum").toString());
        row.createCell(6).setCellValue(map.get("thisReduceSocialNum").toString());
        row.createCell(7).setCellValue(map.get("thisMonthSocialNum").toString());
        row.createCell(8).setCellValue(map.get("lastMonthFundNum").toString());
        row.createCell(9).setCellValue(map.get("thisAddFundNum").toString());
        row.createCell(10).setCellValue(map.get("thisReduceFundNum").toString());
        row.createCell(11).setCellValue(map.get("thisMonthFundNum").toString());
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle3);
        }

        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 14 * 256);
        sheet.setColumnWidth(2, 14 * 256);
        sheet.setColumnWidth(3, 14 * 256);
        sheet.setColumnWidth(4, 16 * 256);
        sheet.setColumnWidth(5, 14 * 256);
        sheet.setColumnWidth(6, 14 * 256);
        sheet.setColumnWidth(7, 16 * 256);
        sheet.setColumnWidth(8, 16 * 256);
        sheet.setColumnWidth(9, 14 * 256);
        sheet.setColumnWidth(10, 14 * 256);
        sheet.setColumnWidth(11, 16 * 256);

    }

}
